package com.mall.product.api.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.mall.model.Product;
import com.mall.model.ProductQuery;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Mapper
public interface ProductMapper extends BaseMapper<Product> {
    //获取总商品数量
    @Select("SELECT COUNT(*) FROM product WHERE created_time BETWEEN #{startDate} AND #{endDate} AND is_del = 0")
    BigDecimal getTotalProductsCount(LocalDate startDate, LocalDate endDate);

    //获取上架商品数
    @Select("SELECT  COUNT(*) FROM product WHERE created_time BETWEEN #{startDate} AND #{endDate} AND status = 1 AND is_del = 0")
    BigDecimal getOnsalesProductsCount(LocalDate startDate, LocalDate endDate);


    //获取库存小于500的商品数
    @Select("SELECT COUNT(*) FROM product WHERE created_time BETWEEN  #{startDate} AND #{endDate} AND stock < 500 and is_del = 0")
    BigDecimal getLowStockProductsCount(LocalDate startDate, LocalDate endDate);

    //获取所有分类的商品统计
    // 获取所有分类的商品统计
    @Select("SELECT c.id as categoryId, c.name as categoryName, COUNT(p.id) as productCount " +
            "FROM category c " +
            "LEFT JOIN product p ON c.id = p.category_id " +
            "WHERE p.created_time BETWEEN #{startDate} AND #{endDate} AND p.is_del = 0 " +
            "GROUP BY c.id, c.name " +
            "ORDER BY productCount DESC")
    List<Map<String, Object>> getAllCategoryProductCount(@Param("startDate") LocalDate startDate,
                                                         @Param("endDate") LocalDate endDate);

    // 获取库存区间商品数
    @Select("SELECT COUNT(*) FROM product WHERE created_time BETWEEN #{startDate} AND #{endDate} AND stock BETWEEN #{minStock} AND #{maxStock} AND is_del = 0")
    BigDecimal getStockRangeProductCount(@Param("minStock") Integer minStock, @Param("maxStock") Integer maxStock,
                                         @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);


    // ==========批量操作相关方法

    /*批量更新商品状态*/
    @Update("UPDATE product SET status = #{status}, update_time = NOW() WHERE id IN (${productIds})")
    int batchUpdateStatus(@Param("productIds") String productIds,@Param("status") Integer status);

    @Update("UPDATE product SET is_hot = #{isHot}, update_time = NOW() WHERE id IN (${productIds})")
    int batchUpdateHot(@Param("productIds") String productIds, @Param("isHot") Integer isHot);


    /*批量删除商品*/
    @Delete("DELETE FROM product WHERE id IN (${productIds})")
    int batchDelete(@Param("productIds") String productIds);


    /*批量减少库存*/
    @Update("UPDATE product SET stock = GREATEST(0, stock - #{amount}), update_time = NOW() WHERE id IN (${productIds})")
    int batchDeductStock(@Param("productIds") String productIds,@Param("amount") Integer amount);

    /**
     * 批量恢复库存
     */
    @Update("UPDATE product SET stock = stock + #{amount}, update_time = NOW() WHERE id IN (${productIds})")
    int batchRestoreStock(@Param("productIds") String productIds, @Param("amount") Integer amount);

    /**
     * 批量设置库存
     */
    @Update("UPDATE product SET stock = #{amount}, update_time = NOW() WHERE id IN (${productIds})")
    int batchSetStock(@Param("productIds") String productIds, @Param("amount") Integer amount);


    /*
    * 根据查询条件来获取商品列表*/
    @Select({
            "SELECT id, name, brand_id, category_id, summary, price, market_price,",
            "       stock, main_pic_url, pic_urls, status, is_hot, description,",
            "       sales_count, create_time, update_time",
            "FROM product",
            "WHERE (#{keyword} IS NULL OR #{keyword} = '' OR name LIKE CONCAT('%', #{keyword}, '%'))",
            "  AND (#{brandId} IS NULL OR brand_id = #{brandId})",
            "  AND (#{categoryId} IS NULL OR category_id = #{categoryId})",
            "  AND (#{status} IS NULL OR status = #{status})",
            "  AND (#{isHot} IS NULL OR is_hot = #{isHot})",
            "ORDER BY create_time DESC"
    })
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "brandId", column = "brand_id"),
            @Result(property = "categoryId", column = "category_id"),
            @Result(property = "summary", column = "summary"),
            @Result(property = "price", column = "price"),
            @Result(property = "marketPrice", column = "market_price"),
            @Result(property = "stock", column = "stock"),
            @Result(property = "mainPicUrl", column = "main_pic_url"),
            @Result(property = "picUrls", column = "pic_urls"),
            @Result(property = "status", column = "status"),
            @Result(property = "isHot", column = "is_hot"),
            @Result(property = "description", column = "description"),
            @Result(property = "salesCount", column = "sales_count"),
            @Result(property = "createTime", column = "create_time"),
            @Result(property = "updateTime", column = "update_time")
    })
    List<Product> selectByQuery(ProductQuery query);

}
